package com.xzqwjw.taskmanager.dao;

import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Environment;

import com.leaderinfo.model.InfoPartyBasic;
import com.leaderinfo.utility.Utility;

public class PartyInfoDao implements com.leaderinfo.service.OperateDatabaseService {

	private SQLiteDatabase objDB;
	private String strDBFile = "";

	public PartyInfoDao(String strDBFile) {
		this.strDBFile = strDBFile;
	}

	// �õ��ղؼ���������Ա��Ϣ�������浽һ��List������
	public List<Map<String, Object>> getRecordsByFavorite() {
		List<Map<String, Object>> listInfo = new ArrayList<Map<String, Object>>();
		Map<String, Object> mapInfo = null;
		InfoPartyBasic objPartyBasicInfo = null;

		objDB = SQLiteDatabase.openOrCreateDatabase(this.strDBFile, null);

		String strSQL = "SELECT * FROM info_cadre_basic WHERE isfavorite=1";
		Cursor objCursor = objDB.rawQuery(strSQL, null);
		if (objCursor.getCount() > 0) {
			while (objCursor.moveToNext()) {
				mapInfo = new HashMap<String, Object>();
				objPartyBasicInfo = new InfoPartyBasic();
				getInfo(objCursor, objPartyBasicInfo);
				mapInfo.put("PartyInfo", objPartyBasicInfo);

				listInfo.add(mapInfo);
			}
		}
		objCursor.close();
		objDB.close();
		return listInfo;
	}

	// �õ�������Ա�ı�ź���ʵ������
	public List<String[]> getAllIDName() {
		List<String[]> listInfo = new ArrayList<String[]>();

		objDB = SQLiteDatabase.openOrCreateDatabase(this.strDBFile, null);

		String strSQL = "SELECT aid,realname FROM info_cadre_basic ORDER BY realname";
		Cursor objCursor = objDB.rawQuery(strSQL, null);
		if (objCursor.getCount() > 0) {
			while (objCursor.moveToNext()) {
				String[] arrIDName = { "", "" };
				arrIDName[0] = objCursor.getString(objCursor.getColumnIndex("aid"));
				arrIDName[1] = objCursor.getString(objCursor.getColumnIndex("realname"));
				listInfo.add(arrIDName);
			}
		}
		objCursor.close();
		objDB.close();
		return listInfo;
	}

	// ���ݱ�Ų�ѯ�õ������Ա����ϸ���ϣ������浽һ��Map������
	@Override
	public Map<String, Object> getRecordByAID(int intAID) {
		InfoPartyBasic objPartyBasicInfo = null;
		Map<String, Object> mapInfo = null;

		objDB = SQLiteDatabase.openOrCreateDatabase(this.strDBFile, null);

		String strSQL = "SELECT * FROM info_cadre_basic WHERE aid=?";
		String[] aryArgs = { String.valueOf(intAID) };
		Cursor objCursor = objDB.rawQuery(strSQL, aryArgs);
		while (objCursor.moveToNext()) {
			objPartyBasicInfo = new InfoPartyBasic();
			getInfo(objCursor, objPartyBasicInfo);
			mapInfo = new HashMap<String, Object>();
			mapInfo.put("PartyInfo", objPartyBasicInfo);
		}
		objCursor.close();
		objDB.close();
		return mapInfo;
	}

	@Override
	public Map<String, Object> getRecordByBasicID(int intBasicID) {
		return null;
	}

	@Override
	public List<Map<String, Object>> getRecordsByBasicID(int intBasicID) {
		return null;
	}

	// �õ����м�¼���浽һ��List������
	@Override
	public List<Map<String, Object>> getAllRecords() {
		List<Map<String, Object>> listInfo = new ArrayList<Map<String, Object>>();
		Map<String, Object> mapInfo = null;
		InfoPartyBasic objPartyBasicInfo = null;

		objDB = SQLiteDatabase.openOrCreateDatabase(this.strDBFile, null);

		String strSQL = "SELECT * FROM info_cadre_basic";
		Cursor objCursor = objDB.rawQuery(strSQL, null);
		if (objCursor.getCount() > 0) {
			while (objCursor.moveToNext()) {
				mapInfo = new HashMap<String, Object>();
				objPartyBasicInfo = new InfoPartyBasic();
				getInfo(objCursor, objPartyBasicInfo);
				mapInfo.put("PartyInfo", objPartyBasicInfo);

				listInfo.add(mapInfo);
			}
		}
		objCursor.close();
		objDB.close();
		return listInfo;
	}

	public List<Map<String, Object>> getPageRecords(int intCurrentPage, int intPageSize) {
		List<Map<String, Object>> listInfo = new ArrayList<Map<String, Object>>();
		Map<String, Object> mapInfo = null;
		InfoPartyBasic objPartyBasicInfo = null;

		int intFirstResult = (intCurrentPage - 1) * intPageSize;
		int intMaxResult = intCurrentPage * intPageSize;

		objDB = SQLiteDatabase.openOrCreateDatabase(this.strDBFile, null);

		String strSQL = "SELECT * FROM info_cadre_basic LIMIT ?,?";
		String[] aryArgs = { String.valueOf(intFirstResult), String.valueOf(intMaxResult) };
		Cursor objCursor = objDB.rawQuery(strSQL, aryArgs);
		if (objCursor.getCount() > 0) {
			while (objCursor.moveToNext()) {
				mapInfo = new HashMap<String, Object>();
				objPartyBasicInfo = new InfoPartyBasic();
				getInfo(objCursor, objPartyBasicInfo);
				mapInfo.put("PartyInfo", objPartyBasicInfo);

				listInfo.add(mapInfo);
			}
		}
		objCursor.close();
		objDB.close();
		return listInfo;
	}

	// ����UnitID�õ��˵�λ�Ĺ�����Ա�б�
	public List<Map<String, Object>> getLeadersByUnitID(int intUnitID) {
		List<Map<String, Object>> listInfo = new ArrayList<Map<String, Object>>();
		Map<String, Object> mapInfo = null;
		int intBasicID = 0;
		String strBirthdayAge = "";
		String strBirthday = "";
		String strYear = "";
		String strAge = "";
		String strIDCardNumber = "";
		String strPhotoPath = "";

		objDB = SQLiteDatabase.openOrCreateDatabase(this.strDBFile, null);

		String strSQL = "SELECT DISTINCT " + "tbasic.aid AS basicid,tbasic.realname AS realname,"
				+ "tbasic.sex AS sex,tbasic.birthday AS birthday,"
				+ "tbasic.birthplace AS birthplace,tbasic.id_card_number AS id_card_number,"
				+ "tunit.job_name AS job_name," + "tbasic.isfavorite AS isfavorite"
				+ " FROM info_cadre_basic AS tbasic "
				+ "LEFT JOIN info_cadre_unit AS tunit ON tbasic.aid=tunit.id_basic "
				+ "WHERE tunit.id_unit=? ORDER BY tunit.leader_order ASC";

		String[] aryArgs = { String.valueOf(intUnitID) };

		Cursor objCursor = objDB.rawQuery(strSQL, aryArgs);
		if (objCursor.getCount() > 0) {
			while (objCursor.moveToNext()) {
				mapInfo = new HashMap<String, Object>();
				// ֻ��ʾ��š�ְ�� ��Ƭ���������Ա𡢳������£����䣩�� ���ᡢ�Ƿ��ղ�
				// ���
				intBasicID = objCursor.getInt(objCursor.getColumnIndex("basicid"));
				mapInfo.put("BasicID", intBasicID);
				// ����
				mapInfo.put("RealName", objCursor.getString(objCursor.getColumnIndex("realname")));
				// �Ա�
				mapInfo.put("Sex", objCursor.getString(objCursor.getColumnIndex("sex")));
				// �������£����䣩
				strBirthday = objCursor.getString(objCursor.getColumnIndex("birthday"));
				strBirthday = Utility.setCNDate(strBirthday);
				if (!strBirthday.equals("")) {
					strYear = strBirthday.substring(0, 4);
					strAge = Utility.getAgeFromBirthday(strYear);
					strBirthdayAge = strBirthday + "��" + strAge + "��";
				}
				mapInfo.put("BirthdayAge", strBirthdayAge);
				// ��Ƭ
				strIDCardNumber = objCursor.getString(objCursor.getColumnIndex("id_card_number"));
				strPhotoPath = Environment.getExternalStorageDirectory() + File.separator + "PartyInfo" + File.separator
						+ "HeadPhoto" + File.separator + strIDCardNumber + ".jpg";
				mapInfo.put("HeadPhotoPath", strPhotoPath);
				// ����
				mapInfo.put("BirthPlace", objCursor.getString(objCursor.getColumnIndex("birthplace")));
				// ְ��
				mapInfo.put("JobName", objCursor.getString(objCursor.getColumnIndex("job_name")));
				// �Ƿ��ղ�
				mapInfo.put("IsFavorite", objCursor.getInt(objCursor.getColumnIndex("isfavorite")));

				listInfo.add(mapInfo);
			}
		}
		objCursor.close();
		objDB.close();

		return listInfo;
	}

	/*
	 * ���·������У����������Ƿ���ӳ�Ա������Ϣ�ͳ�Ա���������֯����׼ȷ������޷�ʹ�ã��������ŵķ���
	 * 
	 * ֻ��һ��UnitID������ ��д��category_unit���в�ѯ�˵�λ����ְprincipalnumber�͸�ְ��deputynumber
	 * 
	 * Ҫ���ݴ˵�λ������ж�һ�´˵�λ��û�й�ڹ��������λ���ж������Ǵ˵�λ�����Ǹ��硢���Ƽ���λ�����ж�����λ���ɼ���λ��û�ж�����λ�ġ�
	 * ������Ϊ�ɼ���λ����ʾ �����Դ���������Ҫ�жϣ��˴�����ע��
	 * 
	 * ����ֻ�е�λ��depth����Ϊ2������ʾ��2�����²���ʾ�����������������ע����жϡ�
	 * 
	 * �쵼�������
	 * 
	 * ��ѯ��ְ�쵼��� ����UnitID��info_cadre_unit�ж�ȡ�˵�λ�µ��쵼������ְ��Ա���ж��������ֶ� //
	 * is_team_member='��' AND job_type='��ְ'
	 * 
	 * ��ѯ��ְ�쵼��� ����UnitID��info_cadre_unit�ж�ȡ�˵�λ�µ��쵼���Ӹ�ְ��Ա���ж��������ֶ� //
	 * is_team_member='��' AND job_type='��ְ'
	 * 
	 * Ȼ��������html�����������úõĸ�ְ����ѭ�������������˸���ְ����ѭ��3�������� //
	 * ����Ҫ�����õĸ�ְ���Ͳ�ѯ�Ľ�������Ƚϣ����Ǹ�����������ĸ������ѭ������������Ҫ�����ȱ��ע��
	 * 
	 * ��ѯ������Ա��� ����UnitID��info_cadre_unit�ж�ȡ�˵�λ�µ�������Ա���ж��������ֶ� //
	 * is_team_member='��'
	 * 
	 * ���ݵ�λ��ŵõ��˵�λ�µ���Ա��Ϣ�������浽һ��List������
	 */

	// ����UnitID�õ��˵�λ����ְ��
	public int countLeadersMainByUnitID(int intUnitID) {
		objDB = SQLiteDatabase.openOrCreateDatabase(this.strDBFile, null);

		String strSQL = "SELECT DISTINCT tbasic.* FROM info_cadre_basic AS tbasic "
				+ "LEFT JOIN info_cadre_unit AS tunit ON tbasic.aid=tunit.id_basic "
				+ "WHERE tunit.holdpost_state='����' AND is_team_member='��' AND job_type='��ְ'"
				+ " AND tunit.id_unit=? ORDER BY tunit.leader_order";

		String[] aryArgs = { String.valueOf(intUnitID) };

		Cursor objCursor = objDB.rawQuery(strSQL, aryArgs);
		int intCount = objCursor.getCount();
		objCursor.close();
		objDB.close();
		return intCount;
	}

	// ����UnitID�õ��˵�λ����ְ��Ա�б�
	public List<Map<String, Object>> getLeadersMainByUnitID(int intUnitID) {
		List<Map<String, Object>> listInfo = new ArrayList<Map<String, Object>>();
		Map<String, Object> mapInfo = null;
		int intBasicID = 0;

		objDB = SQLiteDatabase.openOrCreateDatabase(this.strDBFile, null);

		// String strSQL =
		// "SELECT id_basic FROM info_cadre_unit WHERE id_unit=?";

		String strSQL = "SELECT DISTINCT tbasic.* FROM info_cadre_basic AS tbasic "
				+ "LEFT JOIN info_cadre_unit AS tunit ON tbasic.aid=tunit.id_basic "
				+ "WHERE tunit.holdpost_state='����' AND is_team_member='��' AND job_type='��ְ'"
				+ " AND tunit.id_unit=? ORDER BY tunit.leader_order";

		String[] aryArgs = { String.valueOf(intUnitID) };

		Cursor objCursor = objDB.rawQuery(strSQL, aryArgs);
		if (objCursor.getCount() > 0) {
			while (objCursor.moveToNext()) {
				intBasicID = objCursor.getInt(objCursor.getColumnIndex("aid"));
				mapInfo = getRecordByAID(intBasicID);
				listInfo.add(mapInfo);
			}
		}
		objCursor.close();
		objDB.close();
		return listInfo;
	}

	// ����UnitID�õ��˵�λ���쵼���Ӹ�ְ��
	public int countLeadersViaByUnitID(int intUnitID) {
		objDB = SQLiteDatabase.openOrCreateDatabase(this.strDBFile, null);

		String strSQL = "SELECT DISTINCT tbasic.* FROM info_cadre_basic AS tbasic "
				+ "LEFT JOIN info_cadre_unit AS tunit ON tbasic.aid=tunit.id_basic "
				+ "WHERE tunit.holdpost_state='����' AND is_team_member='��' AND job_type='��ְ'"
				+ " AND tunit.id_unit=? ORDER BY tunit.leader_order";

		String[] aryArgs = { String.valueOf(intUnitID) };

		Cursor objCursor = objDB.rawQuery(strSQL, aryArgs);
		int intCount = objCursor.getCount();
		objCursor.close();
		objDB.close();
		return intCount;
	}

	// ����UnitID�õ��˵�λ���쵼���Ӹ�ְ��Ա�б�
	public List<Map<String, Object>> getLeadersViaByUnitID(int intUnitID) {
		List<Map<String, Object>> listInfo = new ArrayList<Map<String, Object>>();
		Map<String, Object> mapInfo = null;
		int intBasicID = 0;

		objDB = SQLiteDatabase.openOrCreateDatabase(this.strDBFile, null);

		// String strSQL =
		// "SELECT id_basic FROM info_cadre_unit WHERE id_unit=?";

		String strSQL = "SELECT DISTINCT tbasic.* FROM info_cadre_basic AS tbasic "
				+ "LEFT JOIN info_cadre_unit AS tunit ON tbasic.aid=tunit.id_basic "
				+ "WHERE tunit.holdpost_state='����' AND is_team_member='��' AND job_type='��ְ' "
				+ "AND tunit.id_unit=? ORDER BY tunit.leader_order";

		String[] aryArgs = { String.valueOf(intUnitID) };

		Cursor objCursor = objDB.rawQuery(strSQL, aryArgs);
		if (objCursor.getCount() > 0) {
			while (objCursor.moveToNext()) {
				intBasicID = objCursor.getInt(objCursor.getColumnIndex("aid"));
				mapInfo = getRecordByAID(intBasicID);
				listInfo.add(mapInfo);
			}
		}
		objCursor.close();
		objDB.close();
		return listInfo;
	}

	// ����UnitID�õ��˵�λ��������Ա��Ŀ
	public int countLeadersOtherByUnitID(int intUnitID) {
		objDB = SQLiteDatabase.openOrCreateDatabase(this.strDBFile, null);

		String strSQL = "SELECT DISTINCT tbasic.* FROM info_cadre_basic AS tbasic "
				+ "LEFT JOIN info_cadre_unit AS tunit ON tbasic.aid=tunit.id_basic "
				+ "WHERE tunit.holdpost_state='����' AND is_team_member='��'"
				+ " AND tunit.id_unit=? ORDER BY tunit.leader_order";

		String[] aryArgs = { String.valueOf(intUnitID) };

		Cursor objCursor = objDB.rawQuery(strSQL, aryArgs);
		int intCount = objCursor.getCount();
		objCursor.close();
		objDB.close();
		return intCount;
	}

	// ����UnitID�õ��˵�λ��������Ա�б�
	public List<Map<String, Object>> getLeadersOtherByUnitID(int intUnitID) {
		List<Map<String, Object>> listInfo = new ArrayList<Map<String, Object>>();
		Map<String, Object> mapInfo = null;
		int intBasicID = 0;

		objDB = SQLiteDatabase.openOrCreateDatabase(this.strDBFile, null);

		// String strSQL =
		// "SELECT id_basic FROM info_cadre_unit WHERE id_unit=?";

		String strSQL = "SELECT DISTINCT tbasic.* FROM info_cadre_basic AS tbasic "
				+ "LEFT JOIN info_cadre_unit AS tunit ON tbasic.aid=tunit.id_basic "
				+ "WHERE tunit.holdpost_state='����' AND is_team_member='��'"
				+ " AND tunit.id_unit=? ORDER BY tunit.leader_order";

		String[] aryArgs = { String.valueOf(intUnitID) };

		Cursor objCursor = objDB.rawQuery(strSQL, aryArgs);
		if (objCursor.getCount() > 0) {
			while (objCursor.moveToNext()) {
				intBasicID = objCursor.getInt(objCursor.getColumnIndex("aid"));
				mapInfo = getRecordByAID(intBasicID);
				listInfo.add(mapInfo);
			}
		}
		objCursor.close();
		objDB.close();
		return listInfo;
	}

	// ������ѯ

	@Override
	public List<Map<String, Object>> getRecordsByWhere(String strWhere) {
		List<Map<String, Object>> listInfo = new ArrayList<Map<String, Object>>();
		Map<String, Object> mapInfo = null;
		InfoPartyBasic objPartyBasicInfo = null;

		objDB = SQLiteDatabase.openOrCreateDatabase(this.strDBFile, null);
		// strWhere = new String(strWhere.getBytes("gb2312"), "UTF-8");

		String strSQL = "SELECT * FROM info_cadre_basic " + strWhere;
		Cursor objCursor = objDB.rawQuery(strSQL, null);
		if ((objCursor.getCount() > 0) && (objCursor.getCount() < 50)) {
			while (objCursor.moveToNext()) {
				mapInfo = new HashMap<String, Object>();
				objPartyBasicInfo = new InfoPartyBasic();
				getInfo(objCursor, objPartyBasicInfo);
				mapInfo.put("PartyInfo", objPartyBasicInfo);
				listInfo.add(mapInfo);
			}
		}
		objCursor.close();
		objDB.close();
		return listInfo;
	}

	// �Զ���������ѯ

	public List<Map<String, Object>> getRecordsByWhere1(String strWhere) {
		List<Map<String, Object>> listInfo = new ArrayList<Map<String, Object>>();
		Map<String, Object> mapInfo = null;
		InfoPartyBasic objPartyBasicInfo = null;

		objDB = SQLiteDatabase.openOrCreateDatabase(this.strDBFile, null);
		// strWhere = new String(strWhere.getBytes("gb2312"), "UTF-8");

		String strSQL = "SELECT DISTINCT tbasic.* ";
		strSQL += "FROM info_cadre_basic AS tbasic ";
		strSQL += "LEFT JOIN info_cadre_unit AS tunit ON tbasic.aid=tunit.id_basic ";
		strSQL += strWhere;
		// "WHERE tunit.id_job_level=3 AND tbasic.sex='��'"

		Cursor objCursor = objDB.rawQuery(strSQL, null);
		// if ((objCursor.getCount() > 0) && (objCursor.getCount() < 50)) {
		if (objCursor.getCount() > 0) {
			while (objCursor.moveToNext()) {
				mapInfo = new HashMap<String, Object>();
				objPartyBasicInfo = new InfoPartyBasic();
				getInfo(objCursor, objPartyBasicInfo);
				mapInfo.put("PartyInfo", objPartyBasicInfo);
				listInfo.add(mapInfo);
			}
		}
		objCursor.close();
		objDB.close();
		return listInfo;
	}

	// �õ�����ְ����

	// �õ�����ְ�Ʋ��

	public String getIDCardNumber(int intAID) {
		objDB = SQLiteDatabase.openOrCreateDatabase(this.strDBFile, null);

		String strIDCardNumber = "";

		String strSQL = "SELECT id_card_number FROM info_cadre_basic WHERE aid=" + intAID;
		Cursor objCursor = objDB.rawQuery(strSQL, null);
		if (objCursor.getCount() > 0) {
			while (objCursor.moveToNext()) {
				strIDCardNumber = objCursor.getString(objCursor.getColumnIndex("id_card_number"));
			}
		}
		objCursor.close();
		objDB.close();
		return strIDCardNumber;
	}

	public int getFavoriteFlag(int intAID) {
		int intFavoriteFlag = 0;

		objDB = SQLiteDatabase.openOrCreateDatabase(this.strDBFile, null);

		String strSQL = "SELECT isfavorite FROM info_cadre_basic WHERE aid=" + intAID;
		Cursor objCursor = objDB.rawQuery(strSQL, null);
		if (objCursor.getCount() > 0) {
			while (objCursor.moveToNext()) {
				intFavoriteFlag = objCursor.getInt(objCursor.getColumnIndex("isfavorite"));
			}
		}
		objCursor.close();
		objDB.close();
		return intFavoriteFlag;
	}

	public int setFavorite(int intAID) {
		int intFavoriteFlag = getFavoriteFlag(intAID);

		objDB = SQLiteDatabase.openOrCreateDatabase(this.strDBFile, null);

		ContentValues objCV = new ContentValues();
		if (intFavoriteFlag == 0) {
			objCV.put("isfavorite", 1);
		} else {
			objCV.put("isfavorite", 0);
		}
		int intReturn = objDB.update("info_cadre_basic", objCV, "aid=?", new String[] { intAID + "" });

		objDB.close();
		return intReturn;
	}

	private void getInfo(Cursor objCursor, InfoPartyBasic objPartyBasicInfo) {
		objPartyBasicInfo.AID = objCursor.getInt(objCursor.getColumnIndex("aid"));

		objPartyBasicInfo.RealName = objCursor.getString(objCursor.getColumnIndex("realname"));
		objPartyBasicInfo.Sex = objCursor.getString(objCursor.getColumnIndex("sex"));
		objPartyBasicInfo.Birthday = objCursor.getString(objCursor.getColumnIndex("birthday"));
		objPartyBasicInfo.Nation = objCursor.getString(objCursor.getColumnIndex("nation"));
		objPartyBasicInfo.NativePlace = objCursor.getString(objCursor.getColumnIndex("native_place"));

		objPartyBasicInfo.BirthPlace = objCursor.getString(objCursor.getColumnIndex("birthplace"));
		objPartyBasicInfo.Party1Name = objCursor.getString(objCursor.getColumnIndex("party1_name"));
		objPartyBasicInfo.Party1Time = objCursor.getString(objCursor.getColumnIndex("party1_time"));
		objPartyBasicInfo.Party2Name = objCursor.getString(objCursor.getColumnIndex("party2_name"));
		objPartyBasicInfo.Party3Name = objCursor.getString(objCursor.getColumnIndex("party3_name"));

		objPartyBasicInfo.WorkTime = objCursor.getString(objCursor.getColumnIndex("work_time"));
		objPartyBasicInfo.Expertise = objCursor.getString(objCursor.getColumnIndex("expertise"));
		objPartyBasicInfo.Health = objCursor.getString(objCursor.getColumnIndex("health"));
		objPartyBasicInfo.CardNumberID = objCursor.getString(objCursor.getColumnIndex("id_card_number"));
		objPartyBasicInfo.CategoryStaffID = objCursor.getString(objCursor.getColumnIndex("id_category_staff"));

		objPartyBasicInfo.RelationUnit = objCursor.getString(objCursor.getColumnIndex("relation_unit"));
		objPartyBasicInfo.GrassRootsWork = objCursor.getString(objCursor.getColumnIndex("grass_roots_work"));
		objPartyBasicInfo.CategoryManageID = objCursor.getString(objCursor.getColumnIndex("id_category_manage"));
		objPartyBasicInfo.Resume = objCursor.getString(objCursor.getColumnIndex("resume"));
		objPartyBasicInfo.IsFavorite = objCursor.getInt(objCursor.getColumnIndex("isfavorite"));
		objPartyBasicInfo.IsCompare = objCursor.getInt(objCursor.getColumnIndex("iscompare"));
	}

}
